package com.sfc.sdsr.dao;

/*
drop table Address;
drop table Event;
drop table EventAttendance;
drop table EventRole;
drop table KeySignature;
drop table News;
drop table Occupation;
drop table Person;
drop table SfcRole;
drop table Song;
.tables
*/
public enum TableNames {
	OCCUPATION("Occupation", "CREATE TABLE Occupation (" + // working, school, other
			"pk_occupationId INTEGER PRIMARY KEY, " +
			"occupation VARCHAR(35) NOT NULL, " +
			"UNIQUE (occupation)" +
			");"),
	SFC_ROLE("SfcRole", "CREATE TABLE SfcRole (" + // leader, co-leader, events, music, member
			"pk_sfcRoleId INTEGER PRIMARY KEY, " +
			"sfcRole VARCHAR(35) NOT NULL, " +
			"UNIQUE (sfcRole)" +
			");"),
	KEY_SIGNATURE("KeySignature", "CREATE TABLE KeySignature (" + // a b cSharp bFlat
			"pk_keySignatureId INTEGER PRIMARY KEY, " +
			"keySignature VARCHAR(10) NOT NULL, " +
			"UNIQUE (keySignature)" +
			");"),
	EVENT_ROLE("EventRole", "CREATE TABLE EventRole (" + //notAttending = 0 (anything not 0), Attending, (no maybe attending, DISCUSSTHIS), Host, celebrant, foodProvidor, musicProvidor
			"pk_eventRoleId INTEGER PRIMARY KEY, " +
			"eventRole VARCHAR(10) NOT NULL, " +
			"UNIQUE (eventRole)" +
			");"),
			
	ADDRESS("Address", "CREATE TABLE Address (" +
			"pk_addressId INTEGER PRIMARY KEY, " +
			"addressUuid VARCHAR(36) NOT NULL, " + // DEADBEEF-BADD-CAFE-BABE-CAFEBEEFBABE
			"houseNumber SMALLINT NOT NULL, " +
			"streetName VARCHAR(25) NOT NULL, " +
			"modifier VARCHAR(10) NOT NULL, " +
			"city VARCHAR(25), " +
			"state VARCHAR(2), " +
			"zip INTEGER NOT NULL, " +
			"commonName VARCHAR(50), " +
			"extraString VARCHAR(512), " +
			"UNIQUE (houseNumber, streetName, modifier, zip)" +
			");"),
	PERSON("Person", "CREATE TABLE Person (" +
			"pk_personId INTEGER PRIMARY KEY, " +
			"personUuid VARCHAR(36) NOT NULL, " + // DEADBEEF-BADD-CAFE-BABE-CAFEBEEFBABE
			"firstName VARCHAR(25) NOT NULL, " +
			"lastName VARCHAR(25) NOT NULL, " +
			"middleName VARCHAR(25), " +
			"nickName VARCHAR(25), " +
			"fk_address INTEGER, " +
			"fk_occupation INTEGER, " +
			"fk_sfcRole INTEGER, " +
			"birthDate VARCHAR(19), " +
			"joined VARCHAR(19), " + // 2012-01-14 01:16:42, this is UTC
			"extraString VARCHAR(512), " +
			"FOREIGN KEY(fk_address) REFERENCES Address(pk_addressId), " +
			"FOREIGN KEY(fk_occupation) REFERENCES Occupation(pk_occupationId), " +
			"FOREIGN KEY(fk_sfcRole) REFERENCES SfcRole(pk_sfcRoleId), " +
			"UNIQUE (personUuid, firstName, lastName)" +
			");"),
	EVENT("Event", "CREATE TABLE Event (" +
			"pk_eventId INTEGER PRIMARY KEY, " +
			"eventUuid VARCHAR(36) NOT NULL, " + // DEADBEEF-BADD-CAFE-BABE-CAFEBEEFBABE
			"eventName VARCHAR(25), " +
			"reason VARCHAR(25), " +
			"fk_address INTEGER, " +
			"fk_organizer INTEGER, " +
			"webpage VARCHAR(512), " +
			"eventDate VARCHAR(19), " + // 2012-01-14 01:16:42, this is UTC
			"extraString VARCHAR(512), " +
			"FOREIGN KEY(fk_address) REFERENCES Address(pk_addressId), " +
			"FOREIGN KEY(fk_organizer) REFERENCES Person(pk_personId)" +
			"UNIQUE (eventUuid)" +
			");"),
	EVENT_ATTENDANCE("EventAttendance", "CREATE TABLE EventAttendance (" +
			"fk_person INTEGER NOT NULL, " +
			"fk_event INTEGER NOT NULL, " +
			"fk_role INTEGER NOT NULL, " +
			"FOREIGN KEY(fk_person) REFERENCES PERSON(pk_personId), " +
			"FOREIGN KEY(fk_event) REFERENCES EVENT(pk_eventId), " +
			"FOREIGN KEY(fk_role) REFERENCES EVENT_ROLE(pk_eventRoleId), " +
			"UNIQUE (fk_person, fk_event, fk_role)" +
			");"),
	SONG("Song", "CREATE TABLE Song (" +
			"pk_songId INTEGER PRIMARY KEY, " +
			"songUuid VARCHAR(36) NOT NULL, " + // DEADBEEF-BADD-CAFE-BABE-CAFEBEEFBABE
			"title VARCHAR(36), " +
			"artist VARCHAR(36), " +
			"fk_keySignature INTEGER, " +
			"bpm SMALLINT, " +
			"genra VARCHAR(25), " +
			"extraString VARCHAR(512), " +
			"FOREIGN KEY(fk_keySignature) REFERENCES KeySignature(pk_keySignatureId)" +
			"UNIQUE (songUuid)" +
			");"),
	NEWS("News", "CREATE TABLE News (" +
			"pk_newsId INTEGER PRIMARY KEY, " +
			"newsUuid VARCHAR(36) NOT NULL, " + // DEADBEEF-BADD-CAFE-BABE-CAFEBEEFBABE
			"reportDate VARCHAR(19) NOT NULL, " + // 2012-01-14 01:16:42, this is UTC
			"fk_author INTEGER NOT NULL, " +
			"headline VARCHAR(36), " +
			"article VARCHAR(2048), " +
			"extraString VARCHAR(512), " +
			"FOREIGN KEY(fk_author) REFERENCES Person(pk_personId), " +
            "UNIQUE (newsUuid, reportDate, fk_author)" +
			");");
	
	private String createCommand;
	private String tableName;
	
	private TableNames(String tableName, String createCommands) {
		this.tableName = tableName;
		this.createCommand = createCommands;
	}
	
	String getCreateCommand() {
		return createCommand;
	}
	
	String getTableName() {
		return tableName;
	}
}